/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ru.ibrae.excelparser1.mainlogics.dbwork;

import ru.ibrae.cr.client.IDataProvider;
import ru.ibrae.cr.client.Lookup;
import ru.ibrae.cr.domain.BaseDocument;
import ru.ibrae.cr.domain.DocumentDocumentLink;
import ru.ibrae.cr.domain.DocumentDocumentType;
import ru.ibrae.cr.domain.DocumentPersonLink;
import ru.ibrae.cr.domain.DocumentPersonType;
import ru.ibrae.cr.domain.DocumentType;
import ru.ibrae.cr.domain.EventCategory;
import ru.ibrae.cr.domain.Organization;
import ru.ibrae.cr.domain.Person;
import ru.ibrae.cr.domain.PredefinedDocumentType;
import ru.ibrae.cr.orm.AbstractEntity;
import ru.ibrae.cr.orm.Criteria;
import ru.ibrae.cr.orm.Restriction.Operator;
import ru.ibrae.cr.orm.SearchResult;

import ru.ibrae.excelparser1.excelstorageentities.entities.CertificateInfo;
import ru.ibrae.excelparser1.excelstorageentities.entities.CommonInfo;
import ru.ibrae.excelparser1.excelstorageentities.entities.DocumentInfo;
import ru.ibrae.excelparser1.excelstorageentities.entities.ExcelField;
import ru.ibrae.excelparser1.excelstorageentities.entities.MchsLetter;
import ru.ibrae.excelparser1.excelstorageentities.entities.OrganizationInfo;
import ru.ibrae.excelparser1.excelstorageentities.entities.PersonInfo;
import ru.ibrae.excelparser1.excelstorageentities.enumerations.Mark;
import ru.ibrae.excelparser1.mainlogics.CheckResult;
import ru.ibrae.excelparser1.mainlogics.datapush.PushEntity;

import java.awt.HeadlessException;

import java.sql.SQLException;

import java.text.SimpleDateFormat;

import java.util.Date;
import java.util.List;

import javax.swing.JOptionPane;


/**
 *
 * @author pasha
 */
public class Requester {
    //~ Static fields/initializers ---------------------------------------------

    private static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat(
        "yyyy-MM-dd");
    private static final IDataProvider DATA_PROVIDER  = Lookup.getDataProvider();
    private static final String document              = BaseDocument.class.getName();
    private static final String documentDocument      = DocumentDocumentLink.class.getName();
    private static final String documentPerson        = DocumentPersonLink.class.getName();
    private static final String personClass           = Person.class.getName();
    private static final String documentType          = DocumentType.class.getName();

    //~ Methods ----------------------------------------------------------------

    /**
         * Первый поиск удостоверения (см. ТЗ импорта)
         * @param lastName  Фамилия
         * @param certSeries серия удостоверения
         * @param certNumber номер удостоверения
         * @return результат проверки.
         */
    public static CheckResult<AbstractEntity> makeCertSearch1(String lastName,
                                                              String certSeries,
                                                              String certNumber) {
        String hql                     = "select dpl from " + personClass +
                                         " p, " + documentPerson + " dpl, " +
                                         document + " bd, " + documentType +
                                         " dt where p.lastName = '" + lastName +
                                         "' and p.id = dpl.personId " +
                                         "and dpl.documentId = bd.id " +
                                         "and bd.series = '" + certSeries +
                                         "' and bd.number = '" + certNumber +
                                         "' and dpl.type=1 and bd.typeId=dt.id and dt.category is not null";
        List<DocumentPersonLink> links = DATA_PROVIDER.findByHQL(
            DocumentPersonLink.class,
            hql);

        if((links != null) && (links.size() == 1)) {
            return new CheckResult<AbstractEntity>(Boolean.TRUE,
                                                   "",
                                                   links.get(0).getDocument(),
                                                   links.get(0).getPerson());
        } else {
            return new CheckResult<AbstractEntity>(Boolean.FALSE,
                                                   "",
                                                   ( AbstractEntity[] )null);
        }
    }

    public static CheckResult<BaseDocument> makeCertSearch2(String certSeries,
                                                            String certNumber,
                                                            String letterNumber,
                                                            Date letterDate,
                                                            Long orgId) {
        String hql                   = "select cert from " + document +
                                       " reestr, " + document + " cert, " +
                                       documentDocument + " dd, " +
                                       documentType + " dt where" +
                                       " reestr.typeId=6 and cert.typeId=dt.id and dt.category is not null " +
                                       "and dd.document1Id=cert.id and dd.document2Id=reestr.id and dd.type=1 " +
                                       "and cert.series='" + certSeries +
                                       "' and cert.number='" + certNumber +
                                       "' and reestr.number='" + letterNumber +
                                       "' and reestr.issueDate='" +
                                       formatDate(letterDate) +
                                       "' and reestr.issueOrganizationId=" +
                                       orgId;
        List<BaseDocument> findByHQL = DATA_PROVIDER.findByHQL(BaseDocument.class,
                                                               hql);

        if((findByHQL != null) && (findByHQL.size() == 1)) {
            return new CheckResult<BaseDocument>(Boolean.TRUE,
                                                 "",
                                                 findByHQL.get(0));
        }

        return null;
    }

    public static CheckResult<BaseDocument> makeCertSearch3(CertificateInfo certificateInfo,
                                                            MchsLetter mchsLetter,
                                                            Long orgId) {
        PersonInfo person       = mchsLetter.getPerson();
        String hql              = "select c from " + document + " c, " +
                                  document + " z, " + documentDocument +
                                  " dd, " + documentType + " t, " +
                                  personClass + " p, " + documentPerson +
                                  " dp where " +
                                  "c.typeId=t.id and z.typeId=5 and t.category is not null " +
                                  "and dd.document1Id=c.id and dd.document2Id=z.id and dd.type=1" +
                                  " and dp.documentId=z.id and dp.personId=p.id and dp.type=3" +
                                  " and z.issueOrganizationId=" + orgId +
                                  " and c.series='" +
                                  certificateInfo.getSeries() +
                                  "' and c.number='" +
                                  certificateInfo.getNumber() +
                                  "' and p.lastName='" +
                                  person.getSecondName() + "'";
        List<BaseDocument> list = DATA_PROVIDER.findByHQL(BaseDocument.class,
                                                          hql);

        if((list != null) && (list.size() == 1)) {
            return new CheckResult<BaseDocument>(Boolean.TRUE, "", list.get(0));
        }

        return null;
    }

    static Long makeOrgSearch1(CommonInfo info) {
        String hql                       = "select o  from " +
                                           Organization.class.getName() +
                                           " o where o.description like '%ОГРН " +
                                           info.getOgrn() +
                                           "%' and (o.shortname = '" +
                                           info.getShortName() +
                                           "' or o.name = '" +
                                           info.getFullName() + "')";
        List<Organization> organizations = DATA_PROVIDER.findByHQL(
            Organization.class,
            hql);

        return ((organizations != null) && (organizations.size() == 1))
               ? organizations.get(0).getId() : null;
    }

    static CheckResult<Person> makePersonSearch2(ExcelField excelField) {
        PersonInfo person                   = excelField.getMchsLetter()
                                                        .getPerson();
        CertificateInfo previousCertificate = person.getPreviousCertificate();
        String hql                          = "select p from " +
                                              documentPerson + " dpl, " +
                                              personClass + " p, " + document +
                                              " bd, " + documentType +
                                              " t where dpl.documentId=bd.id and dpl.personId=p.id and p.lastName='" +
                                              person.getSecondName() +
                                              "' and bd.series='" +
                                              previousCertificate.getSeries() +
                                              "' and bd.number='" +
                                              previousCertificate.getNumber() +
                                              "' and dpl.type=1 and t.id=bd.typeId and t.category is not null";
        List<Person> persons                = DATA_PROVIDER.findByHQL(Person.class,
                                                                      hql);

        if((persons != null) && (persons.size() == 1)) {
            return new CheckResult<Person>(Boolean.TRUE, "", persons.get(0));
        }

        return null;
    }

    static CheckResult<Person> makePersonSearch3(ExcelField excelField) {
        PersonInfo person         = excelField.getMchsLetter().getPerson();
        DocumentInfo documentInfo = person.getDocumentInfo();
        String hql                = "select p from " + documentPerson +
                                    " dpl, " + document + " d, " + personClass +
                                    " p where dpl.documentId=d.id and dpl.personId=p.id and d.typeId in (1,2) and d.series='" +
                                    documentInfo.getSeries() +
                                    "' and d.number='" +
                                    documentInfo.getNumber() +
                                    "' and p.lastName='" +
                                    person.getSecondName() +
                                    "' and dpl.type=0";
        List<Person> persons      = DATA_PROVIDER.findByHQL(Person.class, hql);

        if((persons != null) && (persons.size() == 1)) {
            return new CheckResult<Person>(Boolean.TRUE, "", persons.get(0));
        }

        return null;
    }

    static Long pushCertificateInfo(PushEntity pushEntity, MchsLetter letter,
                                    OrganizationInfo info)
                             throws Exception
    {
        CertificateInfo excelCertificate = pushEntity.getCertificate();
        final String correctDescription  = "Выдано " + info.getShortName() +
                                           " исх. " + letter.getNumber() +
                                           " от " +
                                           formatDate(letter.getDate()) + "";

        if(pushEntity.getCertificateId() == null) {
            return createCertificate(excelCertificate,
                                     correctDescription,
                                     pushEntity,
                                     true);
        } else {
            Criteria<BaseDocument> criteria = new Criteria<BaseDocument>(
                BaseDocument.class);

            criteria.addRestriction("id",
                                    Operator.equal,
                                    pushEntity.getCertificateId());

            SearchResult<BaseDocument> srBaseDocuments = DATA_PROVIDER.findByCriteria(
                criteria);

            BaseDocument certificate                   = srBaseDocuments.getObjects()
                                                                        .get(0);

            if(excelCertificate.getWhenGiven() != null) {
                if(certificate.getIssueDate() == null) {
                    certificate.setIssueDate(excelCertificate.getWhenGiven());

                    if((certificate.getStatusId() == null) ||
                           (certificate.getStatusId() < 6)) {
                        certificate.setStatusId(Integer.valueOf(6).longValue());
                    }
                } else if(!formatDate(certificate.getIssueDate()).equalsIgnoreCase(
                        formatDate(excelCertificate.getWhenGiven()))) {
                    if(selectCorrectDate(excelCertificate, certificate) == JOptionPane.YES_OPTION) {
                        certificate.setIssueDate(excelCertificate.getWhenGiven());
                    }
                }
            }

            if(excelCertificate.getStatus() != null) {
                Mark excelStatus      = excelCertificate.getStatus();
                Boolean deathMark     = certificate.getDeathMark();
                Boolean duplicateMark = certificate.getDuplicateMark();
                Mark baseStatus;

                if(deathMark && duplicateMark) {
                    baseStatus = Mark.DP;
                } else if(duplicateMark) {
                    baseStatus = Mark.D;
                } else if(deathMark) {
                    baseStatus = Mark.P;
                } else {
                    baseStatus = Mark.NO_MARK;
                }

                if(excelStatus != baseStatus) {
                    certificate = chooseCorrectStatus(certificate,
                                                      excelStatus,
                                                      baseStatus);
                }
            }

            if((certificate.getDescription() == null) ||
                   !certificate.getDescription().equals(correctDescription)) {
                certificate.setDescription(correctDescription);
            }

            certificate.setName(certificate.createCertificateName());

            BaseDocument savedDocument;

            try {
                savedDocument = saveToDB(certificate);
            } catch(Throwable e) {
                throw new Exception("Невозможно сохранить дубликат удостоверения");
            }

            Long id                   = savedDocument.getId();

            return id;
        }
    }

    private static Long createCertificate(CertificateInfo excelCertificate,
                                          final String correctDescription,
                                          PushEntity pushEntity,
                                          boolean currentCertificate)
                                   throws Exception
    {
        BaseDocument certificate = new BaseDocument();
        certificate.setSeries(excelCertificate.getSeries());
        certificate.setNumber(excelCertificate.getNumber());

        if(currentCertificate) {
            certificate.setDescription(correctDescription);
        }

        certificate.setType(
            PredefinedDocumentType.getCertificateType(excelCertificate.getSeries())
                                  .getDocumentType());
        certificate.setEventCategory(
            PredefinedDocumentType.getCertificateType(excelCertificate.getSeries())
                                  .getEventCategory());

        if(excelCertificate.getWhenGiven() != null) {
            certificate.setIssueDate(excelCertificate.getWhenGiven());
        }

        certificate.setStatusId(Long.valueOf(new Integer(6).longValue()));

        if(currentCertificate) {
            certificate.setIssueOrganizationId(pushEntity.getOrganiztionId());
        }

        certificate = setCorrectFlagsByMark(certificate,
                                            excelCertificate.getStatus());
        certificate.setName(certificate.createCertificateName());

        Criteria<BaseDocument> c = new Criteria<BaseDocument>(BaseDocument.class);
        c.addRestriction("name", Operator.equal, certificate.getName());
        c.setMaxResults(1);

        SearchResult<BaseDocument> findByCriteria = DATA_PROVIDER.findByCriteria(c);

        if(findByCriteria != null) {
            if(findByCriteria.getTotalCount() != 0) {
                if(!findByCriteria.getObjects().isEmpty()) {
                    certificate.setId(findByCriteria.getObjects().get(0).getId());
                }
            }
        }

        BaseDocument savedDocument;

        try {
            savedDocument = saveToDB(certificate);
        } catch(Throwable e) {
            throw new Exception("Невозможно сохранить дубликат удостоверения");
        }

        if(pushEntity.getPersonId() != null) {
            DocumentPersonLink dpl = new DocumentPersonLink();
            dpl.setDocumentId(savedDocument.getId());
            dpl.setPersonId(pushEntity.getPersonId());
            saveToDB(dpl);
        }

        return savedDocument.getId();
    }

    private static void createPreviousAndCurrentCertLink(BaseDocument generatedPreviousCertificate,
                                                         Long certificateId) {
        DocumentDocumentLink ddl = new DocumentDocumentLink();
        ddl.setDocument2Id(generatedPreviousCertificate.getId());
        ddl.setDocument1Id(certificateId);
        ddl.setType(DocumentDocumentType.IssuedInSubstitution);

        Criteria<DocumentDocumentLink> c = new Criteria<DocumentDocumentLink>(
            DocumentDocumentLink.class);
        c.addRestriction("document1Id", Operator.equal, ddl.getDocument1Id());
        c.addRestriction("document2Id", Operator.equal, ddl.getDocument2Id());
        c.addRestriction("type", Operator.equal, ddl.getType());

        SearchResult<DocumentDocumentLink> result = DATA_PROVIDER.findByCriteria(c);

        if((result == null) || (result.getObjects() == null) ||
               (result.getTotalCount() == 0)) {
            DATA_PROVIDER.saveObject(ddl);
        }
    }

    private static void cretePreviousCertificateForNewPerson(PersonInfo excelPi,
                                                             Long personId) {
        BaseDocument pCertificate = new BaseDocument();
        pCertificate.setSeries(excelPi.getPreviousCertificate().getSeries());
        pCertificate.setNumber(excelPi.getPreviousCertificate().getNumber());
        pCertificate.setIssueDate(excelPi.getPreviousCertificate().getWhenGiven());
        pCertificate.setName(pCertificate.createCertificateName());

        final SearchResult<DocumentType> certTypeIdBySeries = getCertTypeIdBySeries(
            excelPi.getPreviousCertificate().getSeries());

        if((certTypeIdBySeries != null) &&
               !certTypeIdBySeries.getObjects().isEmpty()) {
            pCertificate.setTypeId(certTypeIdBySeries.getObjects().get(0).getId());
        }

        BaseDocument bd = null;

        try {
            bd = saveToDB(pCertificate);
        } catch(Throwable t) {}

        if(bd != null) {
            DocumentPersonLink dpl = new DocumentPersonLink();
            dpl.setPersonId(personId);
            dpl.setDocumentId(bd.getId());
            dpl.setType(DocumentPersonType.Belongs);

            saveToDB(dpl);
        }
    }

    private static BaseDocument saveToDB(BaseDocument certificate) {
        return DATA_PROVIDER.saveObject(certificate);
    }

    private static int choseWithCustomMessage(String message)
                                       throws HeadlessException
    {
        return JOptionPane.showConfirmDialog(null,
                                             message,
                                             "Требуется реакция пользователя",
                                             JOptionPane.YES_NO_OPTION,
                                             JOptionPane.QUESTION_MESSAGE);
    }

    private static Long createPerson(Long certificateId, Person person,
                                     PersonInfo excelPi,
                                     final Date excelBirthDate)
                              throws SQLException
    {
        boolean canCreate              = false;
        Long personId;
        Criteria<DocumentPersonLink> c = new Criteria<DocumentPersonLink>(
            DocumentPersonLink.class);
        c.addRestriction("documentId", Operator.equal, certificateId);
        c.addRestriction("type", Operator.equal, DocumentPersonType.Belongs);

        SearchResult<DocumentPersonLink> result = DATA_PROVIDER.findByCriteria(c);
        canCreate = (result == null) || (result.getObjects() == null) ||
                    (result.getTotalCount() == 0);

        if(canCreate) {
            person.setFirstName(excelPi.getFirstName());
            person.setLastName(excelPi.getSecondName());
            person.setMiddleName(excelPi.getThirdName());
            person.setAddrAdd(excelPi.getAddress());
            person.setBirthDay(excelBirthDate);
            person.setOldFirstName(excelPi.getOldFirstName());
            person.setOldLastName(excelPi.getOldSecondName());
            person.setOldMiddleName(excelPi.getOldThirdName());

            Person savedPerson = saveToDB(person);
            personId = savedPerson.getId();

            if(excelPi.getPreviousCertificate() != null) {
                cretePreviousCertificateForNewPerson(excelPi, personId);
            }
        } else {
            throw new SQLException(
                "Уже существуют люди, связанные с этим удостоверением");
        }

        return personId;
    }

    private static DocumentPersonLink saveToDB(DocumentPersonLink dpl) {
        return DATA_PROVIDER.saveObject(dpl);
    }

    private static Person saveToDB(Person person) {
        return DATA_PROVIDER.saveObject(person);
    }

    private static void createPreviousCertificate(BaseDocument generatedPreviousCertificate,
                                                  Long personId,
                                                  Long certificateId) {
        SearchResult<DocumentType> certTypeId = getCertTypeIdBySeries(
            generatedPreviousCertificate.getSeries());
        generatedPreviousCertificate.setTypeId(
            ((certTypeId == null) || (certTypeId.getTotalCount() == 0)) ? null
                                                                        : certTypeId.getObjects()
                                                                                    .get(
                0).getId());
        saveToDB(generatedPreviousCertificate);
        createPrevCertAndPersonLink(generatedPreviousCertificate,
                                    personId,
                                    certificateId);
        createPreviousAndCurrentCertLink(generatedPreviousCertificate,
                                         certificateId);
    }

    private static void createPrevCertAndPersonLink(BaseDocument generatedPreviousCertificate,
                                                    Long personId,
                                                    Long certificateId) {
        DocumentPersonLink dpl = new DocumentPersonLink();
        dpl.setDocument(generatedPreviousCertificate);
        dpl.setPersonId(personId);
        dpl.setType(DocumentPersonType.Belongs);
        saveToDB(dpl);
    }

    private static String formatDate(final Date baseBirthDate) {
        return DATE_FORMAT.format(baseBirthDate);
    }

    private static SearchResult<DocumentType> getCertTypeIdBySeries(String certCeries) {
        Criteria<DocumentType> c = new Criteria<DocumentType>(DocumentType.class);
        c.addRestriction("shortName",
                         Operator.equal,
                         "Удостоверение серии " + certCeries);

        SearchResult<DocumentType> documents = DATA_PROVIDER.findByCriteria(c);

        return documents;
    }

    private static int selectCorrectDate(CertificateInfo excelCertificate,
                                         BaseDocument certificate)
                                  throws HeadlessException
    {
        String message = "Разная дата выдачи удостоверения" + "\nсерия:" +
                         excelCertificate.getSeries() + "\nномер:" +
                         excelCertificate.getNumber() +
                         " в файле и в базе данных:" + "\nВ файле: " +
                         formatDate(excelCertificate.getWhenGiven()) +
                         "\nВ базе данных: " +
                         formatDate(certificate.getIssueDate()) +
                         "\nОбновить информацию в базе?";

        return choseWithCustomMessage(message);
    }

    static Long pushPersonInfo(PushEntity pushEntity, Long orgId,
                               MchsLetter letter)
                        throws SQLException, Exception
    {
        PersonInfo excelPi                    = pushEntity.getPerson();
        Long certificateId                    = pushEntity.getCertificateId();
        Long personId;
        Person person                         = new Person();
        DocumentPersonLink documentPersonLink;
        Organization o                        = findOrgById(orgId);
        final Date excelBirthDate             = excelPi.getBirthDate();

        if(pushEntity.getPersonId() == null) {
            personId = createPerson(certificateId, person, excelPi,
                                    excelBirthDate);
            pushEntity.setPersonId(personId);
            personId = updateExistingPerson(pushEntity,
                                            excelPi,
                                            person,
                                            o,
                                            excelBirthDate);
        } else {
            personId = updateExistingPerson(pushEntity,
                                            excelPi,
                                            person,
                                            o,
                                            excelBirthDate);
        }

        documentPersonLink = updateCertificatePersonLink(certificateId, personId);

        if(pushEntity.getPerson().getPreviousCertificate() != null) {
            updatePreviousCertificate(pushEntity, personId);
        }

        return personId;
    }

    private static void updatePreviousCertificate(PushEntity pushEntity,
                                                  Long personId)
                                           throws HeadlessException
    {
        CertificateInfo previousCertificate       = pushEntity.getPerson()
                                                              .getPreviousCertificate();
        BaseDocument generatedPreviousCertificate = new BaseDocument();
        generatedPreviousCertificate.setSeries(previousCertificate.getSeries());
        generatedPreviousCertificate.setNumber(previousCertificate.getNumber());
        generatedPreviousCertificate.setStatusId(
            Long.valueOf(new Integer(6).longValue()));
        generatedPreviousCertificate = setCorrectFlagsByMark(
            generatedPreviousCertificate,
            previousCertificate.getStatus());

        PredefinedDocumentType certificateType = PredefinedDocumentType.getCertificateType(
            generatedPreviousCertificate.getSeries());
        generatedPreviousCertificate.setType(certificateType.getDocumentType());
        generatedPreviousCertificate.setEventCategory(
            certificateType.getEventCategory());
        generatedPreviousCertificate.setName(
            generatedPreviousCertificate.createCertificateName());

        Criteria<BaseDocument> c = new Criteria<BaseDocument>(BaseDocument.class);
        c.addRestriction("name",
                         Operator.equal,
                         generatedPreviousCertificate.getName());
        generatedPreviousCertificate = setCorrectFlagsByMark(
            generatedPreviousCertificate,
            previousCertificate.getStatus());
        generatedPreviousCertificate.setIssueDate(
            previousCertificate.getWhenGiven());

        SearchResult<BaseDocument> findByCriteria = DATA_PROVIDER.findByCriteria(c);

        if((findByCriteria != null) && !findByCriteria.getObjects().isEmpty()) {
            BaseDocument basePrevCert = findByCriteria.getObjects().get(0);

            /*if (( ( previousCertificate.getWhenGiven() != null )
            && ( basePrevCert.getIssueDate() != null )
            && !formatDate(basePrevCert.getIssueDate()).equals(
            formatDate(previousCertificate.getWhenGiven())) )) {
            basePrevCert.setIssueDate(
            ( selectCorrectDate(previousCertificate, basePrevCert) == JOptionPane.YES_OPTION )
            ? previousCertificate.getWhenGiven()
            : basePrevCert.getIssueDate());
            saveToDB(basePrevCert);
            }*/
            generatedPreviousCertificate.setId(basePrevCert.getId());

            BaseDocument savedPreviousCerificate = saveToDB(
                generatedPreviousCertificate);
            createPreviousAndCurrentCertLink(savedPreviousCerificate,
                                             pushEntity.getCertificateId());
        } else {
            createPreviousCertificate(generatedPreviousCertificate,
                                      personId,
                                      pushEntity.getCertificateId());
        }
    }

    private static DocumentPersonLink updateCertificatePersonLink(Long certificateId,
                                                                  Long personId) {
        DocumentPersonLink documentPersonLink;
        Criteria<DocumentPersonLink> criteriaDP = new Criteria<DocumentPersonLink>(
            DocumentPersonLink.class);
        criteriaDP.addRestriction("documentId", Operator.equal, certificateId);
        criteriaDP.addRestriction("personId", Operator.equal, personId);

        SearchResult<DocumentPersonLink> result = DATA_PROVIDER.findByCriteria(
            criteriaDP);

        if((result == null) || (result.getTotalCount() != 1)) {
            DocumentPersonLink dpl = new DocumentPersonLink();
            dpl.setDocumentId(certificateId);
            dpl.setPersonId(personId);
            dpl.setType(DocumentPersonType.Belongs);

            documentPersonLink = saveToDB(dpl);
        } else {
            documentPersonLink = result.getObjects().get(0);
        }

        return documentPersonLink;
    }

    private static Long updateExistingPerson(PushEntity pushEntity,
                                             PersonInfo excelPi, Person person,
                                             Organization o,
                                             final Date excelBirthDate) {
        Long personId             = pushEntity.getPersonId();
        Criteria<Person> criteria = new Criteria<Person>(Person.class);
        criteria.addRestriction("id", Operator.equal, personId);

        SearchResult<Person> personSr = DATA_PROVIDER.findByCriteria(criteria);
        person = personSr.getObjects().get(0);

        boolean smthChanged = false;

        if((excelPi.getThirdName() != null) &&
               ((person.getMiddleName() == null) ||
                   !person.getMiddleName().equals(excelPi.getThirdName()))) {
            person.setMiddleName(excelPi.getThirdName());
            smthChanged = true;
        }

        if((excelPi.getFirstName() != null) &&
               ((person.getFirstName() == null) ||
                   !person.getFirstName().equals(excelPi.getFirstName()))) {
            person.setFirstName(excelPi.getFirstName());
            smthChanged = true;
        }

        if((excelPi.getOldFirstName() != null) &&
               ((person.getOldFirstName() == null) ||
                   !person.getOldFirstName().equals(excelPi.getOldFirstName()))) {
            person.setOldFirstName(excelPi.getOldFirstName());
            smthChanged = true;
        }

        if((excelPi.getOldSecondName() != null) &&
               ((person.getOldLastName() == null) ||
                   !person.getOldLastName().equals(excelPi.getOldSecondName()))) {
            person.setOldLastName(excelPi.getOldSecondName());
            smthChanged = true;
        }

        if((excelPi.getOldThirdName() != null) &&
               ((person.getOldMiddleName() == null) ||
                   !person.getOldMiddleName().equals(excelPi.getOldThirdName()))) {
            person.setOldMiddleName(excelPi.getOldThirdName());
            smthChanged = true;
        }

        if((excelPi.getAddress() != null)) {
            String addrAdd = person.getAddrAdd();

            if((addrAdd == null) ||
                   !addrAdd.equalsIgnoreCase(excelPi.getAddress())) {
                person.setAddrAdd(excelPi.getAddress());
                smthChanged = true;
            }
        }

        final Date baseBirthDate = person.getBirthDay();

        if((excelBirthDate != null) &&
               ((baseBirthDate == null) ||
                   !formatDate(baseBirthDate).equals(formatDate(excelBirthDate)))) {
            person.setBirthDay(excelBirthDate);
            smthChanged = true;
        }

        if(excelPi.getDocumentInfo() != null) {
            updateCertifingDocument(excelPi, person);
        }

        if(smthChanged) {
            Person saveObject = saveToDB(person);
        }

        return personId;
    }

    private static void updateCertifingDocument(PersonInfo excelPi,
                                                Person person) {
        BaseDocument excelBaseDocument = new BaseDocument();
        DocumentInfo documentInfo      = excelPi.getDocumentInfo();
        BaseDocument realBaseDocument  = null;
        excelBaseDocument.setSeries(documentInfo.getSeries());
        excelBaseDocument.setNumber(documentInfo.getNumber());
        excelBaseDocument.setIssueDate(documentInfo.getWhenGiven());
        excelBaseDocument.setIssueOrganizationText(documentInfo.getWhereGiven());

        if(documentInfo.getDocumentType() != null) {
            switch(documentInfo.getDocumentType()) {
                case MILITARY_ID:
                    excelBaseDocument.setTypeId(( long )2);

                    break;

                case PASSPORT:
                    excelBaseDocument.setType(
                        PredefinedDocumentType.Passport.getDocumentType());
                    excelBaseDocument.setEventCategory(
                        PredefinedDocumentType.Passport.getEventCategory());

                    break;

                default:
                    excelBaseDocument.setType(
                        PredefinedDocumentType.Act.getDocumentType());
                    excelBaseDocument.setEventCategory(
                        PredefinedDocumentType.Act.getEventCategory());
            }
        }

        excelBaseDocument.setName(excelBaseDocument.createDocumentName());

        List<BaseDocument> relatedDocuments = person.getAllRelatedDocuments(
            DocumentPersonType.CertifiesForPerson,
            null,
            ( DocumentType[] )null);

        for(BaseDocument baseDocument : relatedDocuments) {
            if(baseDocument.getName()
                               .equalsIgnoreCase(excelBaseDocument.getName())) {
                realBaseDocument = baseDocument;

                break;
            }
        }

        if(realBaseDocument == null) {
            saveToDB(excelBaseDocument);

            DocumentPersonLink dpl = new DocumentPersonLink();
            dpl.setDocument(excelBaseDocument);
            dpl.setType(DocumentPersonType.CertifiesForPerson);
            dpl.setPerson(person);
            saveToDB(dpl);
        } else {
            realBaseDocument.setSeries(excelBaseDocument.getSeries());
            realBaseDocument.setNumber(excelBaseDocument.getNumber());
            realBaseDocument.setIssueDate(excelBaseDocument.getIssueDate());
            realBaseDocument.setIssueOrganizationText(
                excelBaseDocument.getIssueOrganizationText());
            realBaseDocument.setName(realBaseDocument.createDocumentName());
            saveToDB(realBaseDocument);
        }
    }

    private static BaseDocument chooseCorrectStatus(BaseDocument certificate,
                                                    Mark excelMark,
                                                    Mark baseMark) {
        String message = "У удостоверения с серией \"" +
                         certificate.getSeries() + "\" и номером \"" +
                         certificate.getNumber() +
                         "\" различается статус в базе и в файле.\nВ базе: " +
                         getMarkText(baseMark) + "\nВ файле: " +
                         getMarkText(excelMark) + "\nЗаменить в БД?";

        if(choseWithCustomMessage(message) == JOptionPane.YES_OPTION) {
            certificate = setCorrectFlagsByMark(certificate, excelMark);
        }

        return certificate;
    }

    private static String getMarkText(Mark mark) {
        switch(mark) {
            case D:
                return "Дубликат";

            case P:
                return "Посмертно";

            case DP:
                return "Дубликат, посмертно";

            default:
                return "Нет отметки";
        }
    }

    private static BaseDocument setCorrectFlagsByMark(BaseDocument certificate,
                                                      Mark m) {
        switch(m) {
            case D:
                certificate.setDeathMark(Boolean.FALSE);
                certificate.setDuplicateMark(Boolean.TRUE);

                break;

            case DP:
                certificate.setDuplicateMark(Boolean.TRUE);
                certificate.setDeathMark(Boolean.TRUE);

                break;

            case P:
                certificate.setDuplicateMark(Boolean.FALSE);
                certificate.setDeathMark(Boolean.TRUE);

                break;

            case NO_MARK:
                certificate.setDeathMark(Boolean.FALSE);
                certificate.setDuplicateMark(Boolean.FALSE);

                break;
        }

        return certificate;
    }

    static void pushOrganizationInfo(OrganizationInfo oi) {
        Criteria<Organization> c = new Criteria<Organization>(Organization.class);
        c.addRestriction("id", Operator.equal, oi.getOrgId());

        SearchResult<Organization> sro = DATA_PROVIDER.findByCriteria(c);
        Organization o                 = sro.getObjects().get(0);

        if((oi.getFullName() != null) && !oi.getFullName().equals("") &&
               !oi.getFullName().equals(o.getName())) {
            String message = "Отличается полное название организации в файле и в базе.\nВ файле: " +
                             oi.getFullName() + "\nВ базе: " + o.getName() +
                             "\nОбновить информацию в базе?";

            if(choseWithCustomMessage(message) == JOptionPane.YES_OPTION) {
                o.setName(oi.getFullName());
            }
        }

        if((oi.getShortName() != null) && !oi.getShortName().equals("") &&
               !oi.getShortName().equals(o.getShortName())) {
            String message = "Отличается краткое название организации в файле и в базе.\nВ файле: " +
                             oi.getShortName() + "\nВ базе: " +
                             o.getShortName() +
                             "\nОбновить информацию в базе?";

            if(choseWithCustomMessage(message) == JOptionPane.YES_OPTION) {
                o.setShortname(oi.getShortName());
            }
        }

        DATA_PROVIDER.saveObject(o);
    }

    private static Organization findOrgById(Long orgId) {
        Criteria<Organization> criteria = new Criteria<Organization>(
            Organization.class);
        criteria.addRestriction("id", Operator.equal, orgId);

        SearchResult<Organization> findByCriteria = DATA_PROVIDER.findByCriteria(
            criteria);

        return findByCriteria.getObjects().get(0);
    }
}
